Creating a Security Template Permissions Report

This guide will build on the principles from the “Connecting to the Database via Excel” guide and also expect you to connect to the BI.TemplateApplicationComponentPermissions view and create a pivot table as described in the principles of that document. It also assumes you have familiarized yourself with the Introduction document.

1. Follow the Connecting to the Database via Excel guide to create a connection to the DSS database. In a slight departure from that document, we would connect to the BI.TemplateApplicationComponentPermissions view instead of BI.DataSecurityAssignedToUser view.

2. After the data is pulled into a pivot table, there are numerous ways to view this particular report. The below example would show all permissions assigned to a template definition along with the template’s description. Using Excel’s delivered pivot table and standard filters, we could filter this report in numerous ways to glean insights in the data: